import datetime

from sqlalchemy import text

from py.PojosAndDaos.Announcement import Announcement_class

# 貌似前端并未提及该方面的功能，先不实现了
class AnnouncementService:
    @classmethod
    def Announcement_Insert(cls, announce: Announcement_class()):
        session = Announcement_class().session
        announce.date = datetime.date.today()
        session.execute(text("INSERT INTO ANNOUNCEMENT (TITLE, DETAIL, DATE) VALUES ('{}', '{}', '{}')".
                             format(announce.title, announce.detail, announce.date)))
        session.commit()
        session.close()
        return "添加成功！"

    @classmethod
    def Announcement_Query_All(cls):
        announcementList = Announcement_class().session.query(Announcement_class()).all()
        Announcement_class().session.close()
        return announcementList

    @classmethod
    def Announcement_Query_ByDate(cls, datetime: datetime.datetime):
        session = Announcement_class().session
        annountcementList = session.query(Announcement_class()).filter(Announcement_class().date.like(datetime))
        session.close()
        return annountcementList

    @classmethod
    def Announcement_Update(cls, Announcement: Announcement_class()):
        session = Announcement_class().session
        announcement = session.query(Announcement_class()).filter_by(id=Announcement.id).one_or_none()
        if announcement is None:
            return "没有该公告，无法修改"
        announcement.date = datetime.date.today()
        announcement.title = Announcement.title
        announcement.detail = Announcement.detail
        session.commit()
        session.close()
        return "修改公告成功！"

    @classmethod
    def Announcement_Delete_ByDate(cls, date: datetime.datetime):
        session = Announcement_class().session
        session.query(Announcement_class()).filter(Announcement_class().date < date).delete()
        session.commit()
        session.close()
        return "删除成功！"

    @classmethod
    def Announcement_Delete_ByDuration(cls, day):
        session = Announcement_class().session
        announcementList = session.query(Announcement_class()).filter(
            text("DATEDIFF( '{}', DATE ) >= '{}'".format(datetime.date.today(), day))
        ).delete()
        session.commit()
        session.close()
        return "删除成功！"

    @classmethod
    def Announcement_Delete_ByID(cls, ID: int):
        session = Announcement_class().session
        announcement=session.query(Announcement_class()).filter_by(id=ID).one_or_none()
        if announcement is None:
            return "没有该公告"
        session.query(Announcement_class()).filter_by(id=ID).delete()
        session.commit()
        session.close()
        return "删除成功！"


if __name__ == '__main__':
    for announcement in AnnouncementService.Announcement_Query_All():
        print(announcement.__dict__)
